Question 1: Model Formulation and Excel Solution (50 Points)

The manager of a department store in Seattle is attempting to decide on the types and amounts of advertising the store should use. He has invited representatives from the local radio station, television station, and newspaper to make presentations in which they describe their audiences.

The television station representative indicates that a TV commercial, which costs $15,000, would reach 25,000 potential customers. The breakdown of the audience is as follows:

The newspaper representative claims to be able to provide an audience of 10,000 potential customers at a cost of $4,000 per ad. The breakdown of the audience is as follows:

The radio station representative says that the audience for one of the station’s commercials, which costs $6,000, is 15,000 customers. The breakdown of the audience is as follows:

The store has the following advertising policy:

ï‚· Use at least twice as many radio commercials as newspaper ads.

ï‚· Reach at least 100,000 customers.

ï‚· Reach at least twice as many young people as senior citizens.

ï‚· Make sure that at least 30% of the audience is female.

Available space limits the number of newspaper ads to seven. The store wants to know the optimal number of each type of advertising to purchase to minimize total cost.
a. Formulate a linear programming model for this problem. (25 Points)
b. Solve the model by using the computer. (25 Points)

Page 2 of 7

Question 2: Model Formulation and Excel Solution (50 Points)

Problem Statement
The Mill Mountain Coffee Shop blends coffee on the premises for its customers. It sells three basic blends in 1-pound bags, Special, Mountain Dark, and Mill Regular. It uses four different types of coffee to produce the blends—Brazilian, mocha, Colombian, and mild. The shop has the following blend recipe requirements:

The cost of Brazilian coffee is $2.00 per pound, the cost of mocha is $2.75 per pound, the cost of Colombian is $2.90 per pound, and the cost of mild is $1.70 per pound. The shop has 110 pounds of Brazilian coffee, 70 pounds of mocha, 80 pounds of Colombian, and 150 pounds of mild coffee available per week. The shop wants to know the amount of each blend it should prepare each week to maximize profit.

(a) Draw the problem network. (4 Points)

(b) Define the decision variables. (4 Points)

(c) Write the objective Function. (6 Points)

(d) Write the constraints. (16 Points)

(e) Solve the problem in Excel (20 Points)

Page 3 of 7

Question 3: Excel Solution (50 Points)

Problem statement

The Nifty Company specializes in the production of a single product, which it produces in three plants. The product is doing very well, so the company currently is receiving more purchase requests than it can fill. Plans have been made to open an additional plant, but it will not be ready until next year.

For the coming month, four potential customers (wholesalers) in different parts of the country would like to make major purchases. Customer 1 is the company’s best customer, so his full order will be met. Customers 2 and 3 also are valued customers, so the marketing manager has decided that, at a minimum, at least a third of their order quantities should be met. However, she does not feel that Customer 4 warrants special consideration, and so is unwilling to guarantee any minimum amount for this customer. There will be enough units produced to go somewhat above these minimum amounts.

Due largely to substantial variations in shipping costs, the net profit that would be earned on each unit sold varies greatly, depending on which plant is supplying which customer. Therefore, the final decision on how much to send to each customer (above the minimum amounts established by the marketing manager) will be based on maximizing profit. The unit profit for each combination of a plant supplying a customer is shown in Table 1. The rightmost column gives the number of units that each plant will produce for the coming month (a total of 20,000). The bottom row shows the order quantities that have been requested by the customers (a total of 30,000). The next-to-last row gives the minimum amounts that will be provided (a total of 12,000), based on the marketing manager’s decisions described above.

The marketing manager needs to determine how many units to sell to each customer (observing these minimum amounts) and how many units to ship from each plant to each customer to maximize profit.

Figure 1 shows the spreadsheet model for this variant of a transportation problem. Instead of a demand row below the changing cells, we instead have both a minimum row and a maximum row.

Page 4 of 7

Table 1

What’s the range designated for the decision variables in the spreadsheet above?

What’s the formula in cell G11?

What’s the formula in cell C17?

What’s the formula in cell I17?

What’s the LHS of Production constraints that you would enter in Solver?

What’s the RHS of Production constraints that you would enter in Solver?

What’s the operator for the production constraints and why?

What’s the LHS of minimum purchase constraints that you would enter in Solver? Hint:

notice the operator in row 16.

What’s the RHS of minimum purchase constraints that you would enter in Solver?

What’s the LHS of maximum purchase constraints that you would enter in Solver?

What’s the RHS of maximum purchase constraints that you would enter in Solver?

Which cell would you enter in Solver for the “Set Objective” option?

How much inventory would you need to satisfy all demand?

Page 5 of 7

Question 4: Model Formulation and Excel Solution (50 Points)

LP model formulation and computer solution Grading breakdown for this question:
Model Formulation (30 points)

Problem Network: 6 points
Definition of Decision/ Helper variables: 9 points Development of Objective Function: 5 points Development of constraints: 9 points Non-negativity constraints: 1 Points

Computer solution (20 Points)

Decision/ Helper variables section: 5 point O.F. Section: 4 points
Constraints: 8 points
Solver: 3 points

Problem statement

The Jersey Ocean Company manufactures fishing boats. The company purchases the engines it installs in its boats from the Marine Engines Company. Jersey Ocean has the following production schedule for April, May, June, and July:

Marine Engines usually manufactures and ships engines to Jersey Ocean during the month the engines are due. However, from April through July, Marine Engines has a large order with another boat customer, and it can manufacture only 40 engines in April, 60 in May, 90 in June, and 50 in July. Marine Engines has several alternative ways to meet Jersey Ocean’s production schedule:

ï‚· It can produce up to 30 engines in January, February, and March and carry them in inventory at a cost of $50 per engine per month until it ships them to Jersey Ocean. For example, Marine Engines could build an engine in January and ship it to Jersey Ocean in April, incurring $150 in inventory charges.

ï‚· It can also manufacture up to 20 engines in the month they are due on an overtime basis, with an additional cost of $400 per engine. The regular manufacturing cost is $700 per engine.

Marine Engines wants to determine the least costly production schedule that will meet Jersey Ocean’s schedule. The company can use all alternatives simultaneously.

Month

Production

April

60

May

85

June

100

July

120

Page 6 of 7

Hint: Your network includes nodes from January to July.

a. Draw the problem network
b. Define Decision Variables (and helper variables if you decide to use them). c. Develop the Objective Function. Show all the steps of your calculations.
d. Write the constraints
e. Solve the problem using Excel Solver.